package com.hbnu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class FlowerDao {
    public List<Flower> getAllFlowers() {
        List<Flower> list = new ArrayList<>();
        String sql = "SELECT * FROM flowers";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(new Flower(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("type"),
                        rs.getDouble("price"),
                        rs.getInt("stock"),
                        rs.getString("description"),
                        rs.getInt("sold_count")
                ));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /** 根据ID查询单个花卉 */
    public Flower getFlowerById(int id) {
        String sql = "SELECT * FROM flowers WHERE id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new Flower(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("type"),
                            rs.getDouble("price"),
                            rs.getInt("stock"),
                            rs.getString("description"),
                            rs.getInt("sold_count")
                    );
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /** 添加花卉 */
    public boolean addFlower(Flower flower) {
        String sql = "INSERT INTO flowers(name,type,price,stock,description) VALUES(?,?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, flower.getName());
            ps.setString(2, flower.getType());
            ps.setDouble(3, flower.getPrice());
            ps.setInt(4, flower.getStock());
            ps.setString(5, flower.getDescription());
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /** 更新库存和销量（购买时使用） */
    public boolean updateStockAndSoldCount(int flowerId, int buyQty) {
        String checkSql = "SELECT stock FROM flowers WHERE id=?";
        String updateSql = "UPDATE flowers SET stock=stock-?, sold_count=sold_count+? WHERE id=?";
        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);

            // 查询库存是否足够
            int stock = 0;
            try (PreparedStatement ps = conn.prepareStatement(checkSql)) {
                ps.setInt(1, flowerId);
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) stock = rs.getInt("stock");
                }
            }

            if (stock < buyQty) {
                conn.rollback();
                return false;
            }

            // 更新库存和销量
            try (PreparedStatement ps = conn.prepareStatement(updateSql)) {
                ps.setInt(1, buyQty);
                ps.setInt(2, buyQty);
                ps.setInt(3, flowerId);
                ps.executeUpdate();
            }

            conn.commit();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /** 搜索花卉 */
    public List<Flower> searchFlowers(String keyword) {
        List<Flower> list = new ArrayList<>();
        String sql = "SELECT * FROM flowers WHERE name LIKE ? OR type LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new Flower(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("type"),
                            rs.getDouble("price"),
                            rs.getInt("stock"),
                            rs.getString("description"),
                            rs.getInt("sold_count")
                    ));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /** 获取低库存花卉 */
    public List<Flower> getLowStockFlowers(int threshold) {
        List<Flower> list = new ArrayList<>();
        String sql = "SELECT * FROM flowers WHERE stock < ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, threshold);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new Flower(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("type"),
                            rs.getDouble("price"),
                            rs.getInt("stock"),
                            rs.getString("description"),
                            rs.getInt("sold_count")
                    ));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}
